﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using DataAccessService.IService;
using DataAccessService.Repository;
using System.Data;
using DataAccessService.Model;

namespace DataAccessService.Service
{
    public class MsSqlService : BaseRepository, IMsSqlService
    {
        private MsSQLConnectRepository connectRepository = new MsSQLConnectRepository();
        private MsSQLExcuteRepository excuteRepository = new MsSQLExcuteRepository();

        public SqlConnection GetConn()
        {
            try
            {
                ISqliteService sqliteService = new SqliteService();
                List<SysConfig> items = sqliteService.GetList<SysConfig>("type=1");
                SysConfig item = items.FirstOrDefault();

                string connStr = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", item.Server, item.DataBase, item.UserId, item.Psw);
                SqlConnection conn = connectRepository.GetConn(connStr);
                conn.Open();

                return conn;
            }
            catch(Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            return null;
        }

        public SqlConnection GetMasterConn()
        {
            try
            {
                ISqliteService sqliteService = new SqliteService();
                List<SysConfig> items = sqliteService.GetList<SysConfig>("type=1");
                SysConfig item = items.FirstOrDefault();

                string connStr = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", item.Server, "master", item.UserId, item.Psw);
                SqlConnection conn = connectRepository.GetConn(connStr);
                conn.Open();

                return conn;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }

            return null;
        }

        public List<string> GetConnectList()
        {
            ISqliteService sqliteService = new SqliteService();
            List<SysConfig> items = sqliteService.GetList<SysConfig>("type=1");
            SysConfig item = items.FirstOrDefault();

            string connStr = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", item.Server, "master", item.UserId, item.Psw);
            SqlConnection conn = connectRepository.GetConn(connStr);
            conn.Open();

            string sql =string.Format("SELECT spid FROM sysprocesses ,sysdatabases WHERE sysprocesses.dbid=sysdatabases.dbid AND sysdatabases.Name='{0}'", item.DataBase);
                        
            List<string> ids = new List<string>();
            try
            {
                SqlCommand command = new SqlCommand(sql, conn);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    ids.Add(reader.GetInt16(0).ToString());
                }
                reader.Close();
            }catch(Exception ex)
            {

            }
            finally
            {
                conn.Close();
            }

            return ids;
        }

        public List<T> QueryList<T>(string sql)
        {
            SqlConnection conn = GetConn();

            List<T> items = excuteRepository.ExcuteQuerySQL<T>(conn, sql);

            if (null != conn)
                conn.Close();

            return items;
        }

        public List<T> GetList<T>(string whereClause)
        {
            SqlConnection conn = GetConn();
            string sql = string.Format("select * from {0} {1} {2}", typeof(T).Name, string.IsNullOrEmpty(whereClause) ? "" : "where", whereClause);

            List<T> items = excuteRepository.ExcuteQuerySQL<T>(conn, sql);

            if (null != conn)
                conn.Close();

            return items;
        }

        public DataSet QuerySQL(string sql)
        {
            SqlConnection conn = GetConn();
            DataSet dataSet= excuteRepository.ExcuteQuerySQL(conn, sql);
            conn.Close();

            return dataSet;
        }

        public bool ExcuteSql(string sql)
        {
            SqlConnection conn = GetConn();
            SqlCommand command = new SqlCommand(sql, conn);
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                conn.Close();
            }
        }

        public bool ExcuteSql(SqlConnection conn, SqlTransaction tran,string sql)
        {
            SqlCommand command = new SqlCommand(sql, conn, tran);
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
        
        public bool AddItem<T>(T item, string idField)
        {
            SqlConnection conn = GetConn();

            List<string> columns = GetColumnNames(conn, typeof(T).Name);
            string sql = GetInsertSql<T>(item, idField, columns);
            bool result = excuteRepository.ExcuteSQL(conn, sql);

            if (null != conn)
                conn.Close();

            return result;
        }
        
        public bool AddItems<T>(List<T> items,string idField)
        {
            SqlConnection conn = GetConn();

            List<string> columns = GetColumnNames(conn, typeof(T).Name);

            StringBuilder sb = new StringBuilder();
            foreach (var item in items)
            {
                string sql = GetInsertSql<T>(item, idField, columns);
                sb.Append(sql);
                sb.Append(";");
            }
            
            bool result = excuteRepository.ExcuteSQL(conn, sb.ToString());

            if (null != conn)
                conn.Close();

            return result;
        }

        public bool UpdateItem<T>(T item, string idField, string id)
        {
            SqlConnection conn = GetConn();

            List<string> columns = GetColumnNames(conn,typeof(T).Name);
            string sql = GetUpdateSql<T>(item, idField, id, columns);
            bool result = excuteRepository.ExcuteSQL(conn, sql);

            if (null != conn)
                conn.Close();

            return result;
        }

        public bool DeleteItem(string tableName, string idField, string id)
        {
            SqlConnection conn = GetConn();

            string sql = string.Format("delete from {0} where {1}={2}", tableName, idField, id);
            bool result = excuteRepository.ExcuteSQL(conn, sql);

            if (null != conn)
                conn.Close();

            return result;
        }

        public bool AddItem<T>(SqlConnection conn, SqlTransaction tran, T item, string idField)
        {
            List<string> columns = GetColumnNames(conn,tran, typeof(T).Name);
            string sql = GetInsertSql<T>(item, idField, columns);
            bool result = excuteRepository.ExcuteSQL(conn, tran, sql);

            return result;
        }

        public bool DeleteItem(SqlConnection conn, SqlTransaction tran, string tableName, string idField, string id)
        {
            string sql = string.Format("delete from {0} where {1}='{2}'", tableName, idField, id);
            bool result = excuteRepository.ExcuteSQL(conn, tran, sql);

            return result;
        }

        public bool UpdateItem<T>(SqlConnection conn, SqlTransaction tran, T item, string idField, string id)
        {
            List<string> columns = GetColumnNames(conn, tran, typeof(T).Name);
            string sql = GetUpdateSql<T>(item, idField, id, columns);
            bool result = excuteRepository.ExcuteSQL(conn, tran, sql);

            return result;
        }

        /// <summary>
        /// 获取表格字段集合
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private List<string> GetColumnNames(SqlConnection conn,SqlTransaction tran, string tableName)
        {
            string sql = string.Format("select * from {0} where 1=2", tableName);
            DataSet dataSet = excuteRepository.ExcuteQuerySQL(conn,tran, sql);

            List<string> columnsList = new List<string>();
            foreach (DataColumn column in dataSet.Tables[0].Columns)
            {
                columnsList.Add(column.ColumnName);
            }
            return columnsList;
        }

        /// <summary>
        /// 获取表格字段集合
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private List<string> GetColumnNames(SqlConnection conn, string tableName)
        {
            string sql = string.Format("select * from {0} where 1=2", tableName);
            DataSet dataSet = excuteRepository.ExcuteQuerySQL(conn,sql);

            List<string> columnsList = new List<string>();
            foreach (DataColumn column in dataSet.Tables[0].Columns)
            {
                columnsList.Add(column.ColumnName);
            }
            return columnsList;
        }

        /// <summary>
        /// DataSet转实体
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public List<T> ConvertDataSetToEntity<T>(DataSet dataSet)
        {
            List<T> items = excuteRepository.ConvertToEntity<T>(dataSet);
            return items;
        }
    }
}
